package com.company.dao.imp;

import com.company.dao.PersonDAO;
import com.company.entity.Person;

import java.sql.*;
import java.util.ArrayList;

/**
 * Created by xujc on 7/15/15.
 */
public class PersonDAOImp implements PersonDAO {
    public static final String URL="jdbc:mysql://localhost:3306/neu";
    public static final String USERNAME="root";
    public static final String PASSWORD="";

    private Connection connection;

    public void makeConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(URL, USERNAME, null);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void close(){
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Person getPerson(int id) {
        Person person=null;
        ResultSet resultSet=null;
        PreparedStatement statement=null;
        try {
            String sql = "SELECT * FROM person WHERE id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            resultSet.next();
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String couple = resultSet.getString("couple");
            person = new Person(id, name, age, couple);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return person;
    }


    @Override
    public Person[] getPersonsByName(String name) {
        ArrayList<Person> person=new ArrayList<>();
        ResultSet resultSet=null;
        PreparedStatement statement=null;
        try {
            String sql = "SELECT * FROM person WHERE name = ?";
            connection = DriverManager.getConnection(URL, USERNAME, null);
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                int age = resultSet.getInt("age");
                String couple = resultSet.getString("couple");
                person.add(new Person(id, name, age, couple));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return ((Person[]) person.toArray());
    }

    @Override
    public void delete(int id) {
        PreparedStatement statement=null;
        try {
            String sql = "DELETE FROM person WHERE id = ?";
            connection = DriverManager.getConnection(URL, USERNAME, null);
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    @Override
    public void setInfo(Person person) {
        PreparedStatement statement=null;
        try {
            String sql = "UPDATE person SET name = ?,age = ?,couple = ? WHERE id = ?";
            connection = DriverManager.getConnection(URL, USERNAME, null);
            statement = connection.prepareStatement(sql);
            statement.setString(1, person.getName());
            statement.setInt(2, person.getAge());
            statement.setString(3, person.getCouple());
            statement.setInt(4, person.getId());
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    @Override
    public void insert(String name, int age, String couple) {
        PreparedStatement statement=null;
        try {
            String sql = "INSERT INTO person (name, age, couple) VALUES (?,?,?)";
            connection = DriverManager.getConnection(URL, USERNAME, null);
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            statement.setInt(2, age);
            statement.setString(3, couple);
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }
}
